package com.danco.training.zaneuskaya.connection.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import com.danco.training.zaneuskaya.connection.dao.AbstractDao;
import com.danco.training.zaneuskaya.connection.dao.OrderDao;
import com.danco.training.zaneuskaya.domain.Book;
import com.danco.training.zaneuskaya.domain.Order;

public class OrderDaoImpl extends AbstractDao<Order, Integer> implements
		OrderDao {

	public static String readOrderQuery = "SELECT * FROM orders WHERE  O_ID = ?";
	public static String updateOrderQuery = "UPDATE orders SET EXECUTED = ?, ORDER_DATE = ?, TOTAL_PRICE = ?  WHERE O_ID = ?";
	public static String insertOrderQuery = "INSERT INTO orders (SET EXECUTED , ORDER_DATE, TOTAL_PRICE ) VALUES (?,?,?) ;";
	public static String insertOrderQuery2 = "INSERT INTO order_book (ORDER_ID, BOOK_ID) VALUES (?,?);";
	public static String executedOrdersQuery = "SELECT * FROM orders WHERE EXECUTED = ?;";
	public static String executedOrdersForPeriodQuery = "SELECT * FROM orders WHERE EXECUTED = 1 AND ORDER_DATE BETWEEN ? AND ?;";
	public static String sortedOrdersByOrderDate = "SELECT * FROM orders ORDER BY ORDER_DATE ";
	public static String sortedOrdersByTotal = "SELECT * FROM orders ORDER BY TOTAL_PRICE ";
	public static String sortedOrdersByState = "SELECT * FROM orders ORDER BY EXECUTED ";
	public static String sortedExecutedOrdersByOrderDate = "SELECT * FROM orders WHERE EXECUTED = 1 AND ORDER_DATE BETWEEN ? AND ? ORDER BY ORDER_DATE";
	public static String sortedExecutedOrdersByPrice = "SELECT * FROM orders WHERE EXECUTED = 1 HAVING ORDER_DATE BETWEEN ? AND ? ORDER BY TOTAL_PRICE";
	public static String addBookInOrderQuery = "INSERT INTO order_book (BOOK_ID, ORDER_ID) VALUES (?,?);";
	public static String deleteBookFromOrderQuery = "DELETE FROM order_book WHERE ORDER_ID = ? AND BOOK_ID = ?;";
	public static String executeOrderQuery = "UPDATE orders SET EXECUTED = 1  WHERE O_ID = ?";
	public static String readAllQuery = "SELECT * FROM orders;";
	public static String deleteQuery = "DELETE FROM orders WHERE  O_ID = ?;";
	public static String findByBookQuery = "SELECT * FROM orders WHERE O_ID = (SELECT ORDER_ID FROM order_book WHERE BOOK_ID = ?)";
	public static String deleteByBookQuery = "DELETE FROM order_book WHERE BOOK_ID = ?";
	public static String findBooksByOrder = "SELECT books.* FROM books,order_book WHERE books.B_ID = order_book.BOOK_ID AND ORDER_ID = ?";

	private static final Logger logger = Logger.getLogger(OrderDaoImpl.class);

	@Override
	public List<Order> readExecutedOrders(Connection connection) {
		List<Order> orders = new ArrayList<Order>();
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement(executedOrdersQuery);
			preparedStatement.setInt(1, 1);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				orders.add(convertResultSet(resultSet));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}

		return orders;
	}

	@Override
	public List<Order> readNotExecutedOrders(Connection connection) {
		List<Order> orders = new ArrayList<Order>();
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement(executedOrdersQuery);
			preparedStatement.setInt(1, 0);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				orders.add(convertResultSet(resultSet));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return orders;
	}

	@Override
	public List<Order> readExecutedOrdersForPeriod(Connection connection,
			Date from, Date to) {
		List<Order> orders = new ArrayList<Order>();
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement(executedOrdersForPeriodQuery);
			preparedStatement.setDate(1, new java.sql.Date(from.getTime()));
			preparedStatement.setDate(2, new java.sql.Date(to.getTime()));
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				orders.add(convertResultSet(resultSet));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}

		return orders;
	}

	@Override
	public List<Order> readOrdersSortedByOrderDate(Connection connection) {
		List<Order> orders = new ArrayList<Order>();
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement(sortedOrdersByOrderDate);
			
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				orders.add(convertResultSet(resultSet));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}

		return orders;
	}

	@Override
	public List<Order> readOrdersSortedByTotalPrice(Connection connection) {
		List<Order> orders = new ArrayList<Order>();
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement(sortedOrdersByTotal);
			
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				orders.add(convertResultSet(resultSet));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}

		return orders;
	}

	@Override
	public List<Order> readOrdersSortedByOrderState(Connection connection) {
		List<Order> orders = new ArrayList<Order>();
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement(sortedOrdersByState);
			
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				orders.add(convertResultSet(resultSet));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}

		
		return orders;
	}

	@Override
	public List<Order> readExecutedOrdersSortedByOrderDate(
			Connection connection, Date from, Date to) {
		List<Order> orders = new ArrayList<Order>();
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement(sortedExecutedOrdersByOrderDate);
			preparedStatement.setDate(1,  new java.sql.Date(from.getTime()));
			preparedStatement.setDate(2,  new java.sql.Date(to.getTime()));
			
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				orders.add(convertResultSet(resultSet));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}

		
		return orders;
	}

	@Override
	public List<Order> readExecutedOrdersSortedByPrice(Connection connection,
			Date from, Date to) {
		List<Order> orders = new ArrayList<Order>();
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement(sortedExecutedOrdersByPrice);
			preparedStatement.setDate(1, new java.sql.Date(from.getTime()));
			preparedStatement.setDate(2, new java.sql.Date(to.getTime()));
			
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				orders.add(convertResultSet(resultSet));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}

		
		return orders;
	}

	@Override
	public boolean addBookOrder(Connection connection, Order order, Book book) {
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement(addBookInOrderQuery);
			preparedStatement.setInt(2, order.getId());

			preparedStatement.setInt(1, book.getId());
			preparedStatement.executeUpdate();
			return true;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return false;
	}

	@Override
	public boolean removeBookOrder(Connection connection, Order order, Book book) {
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement(deleteBookFromOrderQuery);
			preparedStatement.setInt(1, order.getId());

			preparedStatement.setInt(2, book.getId());
			preparedStatement.executeUpdate();
			return true;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return false;
	}

	@Override
	public boolean executeOrder(Connection connection, Integer id) {
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement(executeOrderQuery);
			preparedStatement.executeUpdate();
			return true;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);

		}
		return false;
	}

	@Override
	public Order convertResultSet(ResultSet resultSet) throws SQLException {
		List<Book> books = new ArrayList<Book>();
		int id = resultSet.getInt("O_ID");
		int executed = resultSet.getInt("EXECUTED");
		Date date = resultSet.getDate("ORDER_DATE");
		double total = resultSet.getDouble("TOTAL_PRICE");
		boolean ex = false;
		if (executed == 1) {
			ex = true;
		}
		Order order = new Order(id, books, ex, date, total);
		return order;
	}

	@Override
	public boolean existsInDB(Connection connection, Order object)
			throws SQLException {
		if (read(connection, object.getId()) != null) {
			return true;
		}
		return false;
	}

	@Override
	public PreparedStatement getUpdateStatement(Connection c, Order object)
			throws SQLException {
		PreparedStatement preparedStatement = c
				.prepareStatement(updateOrderQuery);

		int ex = 0;
		if (object.isExecuted()) {
			ex = 1;
		}
		preparedStatement.setInt(1, ex);
		preparedStatement.setDate(2, new java.sql.Date(object.getOrderDate().getTime()));
		preparedStatement.setDouble(3, object.getTotalPrice());
		preparedStatement.setInt(4, object.getId());

		return preparedStatement;
	}

	@Override
	public PreparedStatement getInsertStatement(Connection c, Order object)
			throws SQLException {
		PreparedStatement preparedStatement = c
				.prepareStatement(insertOrderQuery);

		int ex = 0;
		if (object.isExecuted()) {
			ex = 1;
		}
		preparedStatement.setInt(1, ex);
		preparedStatement.setDate(2, new java.sql.Date(object.getOrderDate().getTime()));
		preparedStatement.setDouble(3, object.getTotalPrice());

		for (Book book : object.getBooks()) {
			preparedStatement.addBatch(insertOrderQuery2);
			preparedStatement.setInt(4, object.getId());
			preparedStatement.setInt(5, book.getId());
		}

		preparedStatement.executeQuery();
		return preparedStatement;
	}

	@Override
	public PreparedStatement getLastInsertedStatement(Connection c)
			throws SQLException {
		PreparedStatement preparedStatement = c
				.prepareStatement(readOrderQuery);
		preparedStatement.setString(1, "LAST_INSERT_ID()");
		return preparedStatement;
	}

	@Override
	public PreparedStatement readStatement(Connection c, Integer PK)
			throws SQLException {
		PreparedStatement preparedStatement = c
				.prepareStatement(readOrderQuery);
		preparedStatement.setInt(1, PK);
		return preparedStatement;

	}

	@Override
	public PreparedStatement readAllStatement(Connection c) throws SQLException {
		PreparedStatement preparedStatement = c.prepareStatement(readAllQuery);

		return preparedStatement;
	}

	@Override
	public PreparedStatement getDeleteStatement(Connection c, Order object)
			throws SQLException {
		PreparedStatement preparedStatement = c.prepareStatement(deleteQuery);
		preparedStatement.setInt(1, object.getId());

		return preparedStatement;
	}

	@Override
	public PreparedStatement getDeleteByIdStatement(Connection c, Integer PK)
			throws SQLException {
		PreparedStatement preparedStatement = c.prepareStatement(deleteQuery);
		preparedStatement.setInt(1, PK);

		return preparedStatement;
	}

	@Override
	public List<Order> findOrderByBook(Connection connection, Book book) {
		List<Order> orders = new ArrayList<Order>();
		try(PreparedStatement preparedStatement = connection.prepareStatement(findByBookQuery);){
			preparedStatement.setInt(1, book.getId());
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()){
				orders.add(convertResultSet(rs));
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return orders;
	}

	@Override
	public boolean deleteOrderByBookFromOrderBook(Connection connection,
			Book book) {
		try(PreparedStatement preparedStatement = connection.prepareStatement(deleteByBookQuery);){
			preparedStatement.setInt(1, book.getId());
			preparedStatement.executeUpdate();
			return true;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return false;
	}

	@Override
	public List<Book> findBooksByOrderId(Connection connection, Integer id) {
		List <Book> books = new ArrayList<Book>();
		try(PreparedStatement preparedStatement = connection.prepareStatement(findBooksByOrder);){
			preparedStatement.setInt(1, id);
			ResultSet resultSet = preparedStatement.executeQuery();
		while (resultSet.next()) {
			Book book = null;
			int bookId = resultSet.getInt("B_ID");
			String title = resultSet.getString("TITLE");
			String author = resultSet.getString("AUTHOR");
			Date publicationDate = resultSet.getDate("PUB_DATE");
			double price = resultSet.getDouble("PRICE");
			Boolean available = resultSet.getBoolean("AVAILABLE");
			int quantity = resultSet.getInt("QUANTITY");
			Date receiptDate = resultSet.getDate("REC_DATE");
			book = new Book(bookId, title, author, publicationDate, price,
					available, quantity, receiptDate);
			books.add(book);

		}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
		return books;
	}

	

}
